<?php
/** * Directory_company_model Class *
 
 */
class Directory_company_model extends MY_Model {
  function Directory_company_model(){
    parent::MY_Model();
    $this->directory_company_table = "directory_company";
    $this->setTableName($this->directory_company_table);
    $this->directory_user_table = "directory_user";
    $this->directory_services_table = "directory_services";
    $this->directory_services_relationship_table = "directory_services_relationship";
    $this->directory_news_table = "directory_news";
    $this->directory_photo_table = "directory_photo";
    $this->directory_branch_table = "directory_branch";
    $this->directory_invoice_table = "directory_invoice";
    $this->directory_subscription_type_table = "directory_subscription_type";
    $this->city_table = "city";
    $this->region_table = "region";
    $this->logo_folder_url = site_url('uploads/directory/company_logo/');
    $this->logo_thumb_folder_url = site_url('uploads/directory/company_logo/thumb/');
    $this->photos_folder_url = site_url('uploads/directory/photo/');
    $this->photos_thumb_folder_url = site_url('uploads/directory/photo/thumb/');
    $this->news_folder_url = site_url('uploads/directory/news/');
    $this->news_thumb_folder_url = site_url('uploads/directory/news/thumb/');
  }
  function get_newest_company($limit= 10, $offset = 0, $province = '', $services = '', $search = '', $weight=NULL, $height=NULL){      
       
        if(!empty($services) && $services != "'all'"){
            
            $sql_services = "WHERE id_services IN ($services) ";            
        }
        /*$sql = "SELECT d_user.paid_until , d_company.*, region.name as region_name , province_name,
                city.name as city_name_,city_name ";*/
        $sql = "SELECT d_user.paid_until , d_company.* ";
        $sql .= "FROM directory_company AS d_company ";
        $sql .= "LEFT JOIN directory_user AS d_user ON d_user.id = d_company.id ";
        $sql .= "LEFT JOIN region AS region ON region.id = d_company.region_id ";
        //$sql .= "LEFT JOIN city AS city ON city.id = d_company.city_id ";
        //$sql .= "LEFT JOIN ( SELECT id_company ";
        $sql .= "INNER JOIN ( SELECT id_company ";
        $sql .= "FROM directory_services_relationship ";
        //$sql .= "WHERE id_services IN ('1') ";
        if(!empty($sql_services)){
            $sql .= $sql_services;
        }
        
        $sql .= "GROUP BY id_company) AS rel ON rel.id_company = d_company.id ";        
        //$sql .= "WHERE d_user.paid_until > NOW() ";
        $sql .= " WHERE d_user.activation_status ='1' ";
        if((!empty($province)) && $province != 'all_province'){
            $sql_province = "AND region.id = $province ";
            $sql .= $sql_province;
        }
        if(!empty($search)){
            $sql_search = "AND d_company.company LIKE '%$search%' ";
            $sql .= $sql_search;
        }
        if($weight)
		{
			$sql.= ' and (d_company.weight between '.$weight['start'].' and '.$weight['stop'].')';
		}
		
		if($height)
		{
			$sql.= ' and (d_company.height between '.$height['start'].' and '.$height['stop'].')';
		}
        //$sql .= "AND region.id = 3684;";
        $sql .= "LIMIT $offset, $limit";
        $query = $this->db->query($sql);
        //echo ($this->db->last_query());
        if ($query->num_rows() > 0) {
            $result = $query->result_array();            
            return $result;
            //return $this->db->last_query();
        } else {
            return false;
        }
  }
  
  function get_count_newest_company($province = '', $services = '', $search = ''){    
        if((!empty($services)) && ($services != "'all'")){
            $sql_services = "WHERE id_services IN ($services) ";
        }
        $sql = "SELECT count(*) as count ";
        $sql .= "FROM directory_company AS d_company ";
        $sql .= "LEFT JOIN directory_user AS d_user ON d_user.id = d_company.id ";
        $sql .= "LEFT JOIN region AS region ON region.id = d_company.region_id ";
        $sql .= "LEFT JOIN city AS city ON city.id = d_company.city_id ";
        //$sql .= "LEFT JOIN ( SELECT id_company ";
        $sql .= "INNER JOIN ( SELECT id_company ";
        $sql .= "FROM directory_services_relationship ";
        //$sql .= "WHERE id_services IN ('1') ";
        if(!empty($sql_services)){
            $sql .= $sql_services;
        }
        $sql .= "GROUP BY id_company) AS rel ON rel.id_company = d_company.id ";
        
        //$sql .= "WHERE d_user.paid_until > NOW() ";
        if((!empty($province)) && $province != 'all_province'){
            $sql_province = "AND region.id = $province ";
            $sql .= $sql_province;
        }
        if(!empty($search)){
            $sql_search = "AND d_company.company LIKE '%$search%' ";
            $sql .= $sql_search;
        }
        //$sql .= "AND region.id = 3684;";
        //$sql .= "LIMIT $offset, $limit";
        $query = $this->db->query($sql);        
        //die($this->db->last_query());
        //return $this->db->last_query();
        
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            return $result;
        } else {
            return false;
        }
    //}
  }
  /*
   * method get_company_by_slug 
   * @description untuk mengambil HANYA 1 row dari database dengan ketentuan berupa slug-name (biasanya id)
   * slug-name berupa string dengan ketentuan seperti slug-name pada umumnya
   * @why karena gerald minta SEO friendly, ini adalah salah satu syaratnya, juga keterbacaan oleh user
   * @param $company_slug (string) slug name yang di cari
   * @return Mixed, berisi row yang memenuhi ketentuan pada input, dan FALSE jika gagal
   *
   * NOTE: slug name itu sendiri tidak mungkin ada yang ganda, karena pada saat save ada pemeriksaan nama terlebih dahulu
   * dapat dilihat di fungsi [lengkapi]
   */
  function get_company_by_slug($company_slug){    
    if (!empty($company_slug)) {
       
    $directory_user = $this->directory_user_table;
    $directory_company = $this->directory_company_table;
    $this->db->select("{$directory_user}.email as company_email,{$directory_user}.paid_until as paid_until , {$directory_company}.*");
    $this->db->from($directory_user);
    $this->db->join($directory_company,"{$directory_company}.id = {$directory_user}.id",'left');
    $this->db->where("{$directory_company}.company_slug_name = '{$company_slug}' ");
    $this->db->order_by("{$directory_user}.id",'DESC');
    $this->db->limit(1);
    $query = $this->db->get();    
        if ($query->num_rows() > 0) {
        $result = $query->row_array();
            return $result;
        } else {
            return false;
        }
    }
    
    
  }
  function get_company_by_id($company_id){
    if (!empty($company_id)) {
    $directory_user = $this->directory_user_table;
    $directory_company = $this->directory_company_table;
    $this->db->select("{$directory_user}.email as company_email ,{$directory_user}.paid_until as paid_until , {$directory_company}.*");
    $this->db->from($directory_user);
    $this->db->join($directory_company,"{$directory_company}.id = {$directory_user}.id",'left');
    $this->db->where("{$directory_company}.id = '{$company_id}' ");
    $this->db->order_by("{$directory_user}.id",'DESC');
    $this->db->limit(1);
    $query = $this->db->get();

        if ($query->num_rows() > 0) {
        $result = $query->row_array();
            return $result;
        } else {
            return false;
        }
    }
    
  }
  function update_company($company_id, $company_name, $company_desc, $company_website,
        $contact_number, $mobile, $fax, $contact_email, $company_logo, $province_id, $city_name, $height='', $weight='', $age=''){
        $this->setTableName($this->directory_company_table);   
        $slug_name = url_title($company_name,'dash',TRUE);
        /*$data = array('company' => $company_name, 'company_slug_name' => $slug_name,
                'company_description' => $company_desc, 'company_contact_number' => $contact_number,
                'company_website' => $company_website, 'mobile_number' => $mobile, 'fax_number' =>
                $fax, 'contact_person' => $contact_email, 'company_logo' => $company_logo,
                'province_name'=>$province_name, 'city_name'=>$city_name, 'last_edited' => date('Y-m-d H:i:s') );*/
          $data = array('company' => $company_name, 'company_slug_name' => $slug_name,
                'company_description' => $company_desc, 'company_contact_number' => $contact_number,
                'company_website' => $company_website, 'mobile_number' => $mobile, 'fax_number' =>
                $fax, 'company_contact_email' => $contact_email, 'company_logo' => $company_logo,
                'region_id'=>$province_id,'province_name' => $this->get_region_name($province_id), 'city_name'=>$city_name, 'last_edited' => date('Y-m-d H:i:s'), 'height' => $height, 'weight' => $weight, 'age' => $age  );     
                            
        if ($this->getDetail('','id="'.$company_id.'"')){
            $result = $this->update($company_id,$data);
            
        }else{
            $data['id'] = $company_id;
            $result = $this->insert($data);
        }
                 
        return $result;
  }
  function update_location($company_id, $physical_street, $physical_suburb, $physical_city,
        $physical_zipcode, $postal_street, $postal_suburb, $postal_city, $postal_zipcode,
        $latitude, $longitude, $country_name,$country_id = '', $region_id = '', $city_id = '') {
       /* $data = array('physical_street' => $physical_street, 'physical_suburb' => $physical_suburb,
            'physical_city' => $physical_city, 'physical_zipcode' => $physical_zipcode,
            'postal_street' => $postal_street, 'postal_suburb' => $postal_suburb,
            'postal_city' => $postal_city, 'postal_zipcode' => $postal_zipcode, 'latitude' =>
            $latitude, 'longitude' => $longitude, 'country_id' => $country_id, 'region_id' =>
            $region_id, 'city_id' => $city_id, 'country_name'=>$country_name );*/
        $data = array('physical_street' => $physical_street, 'physical_suburb' => $physical_suburb,
            'physical_city' => $physical_city, 'physical_zipcode' => $physical_zipcode,
            'postal_street' => $postal_street, 'postal_suburb' => $postal_suburb,
            'postal_city' => $postal_city, 'postal_zipcode' => $postal_zipcode, 'latitude' =>
            $latitude, 'longitude' => $longitude, 'country_id' => $country_id, 'city_id' => $city_id, 'country_name'=>$country_name );
    $this->db->where('id',$company_id);
    $this->db->update($this->directory_company_table,$data);
    return $this->db->last_query();
    }

  function get_company_services($company_id, $services_category = 'products'){
    if (!empty($company_id)) {
    $services = $this->directory_services_table;
    $relation = $this->directory_services_relationship_table;
    $this->db->from($relation);
    $this->db->join($services," {$services}.id = {$relation}.id_services",'left');
    $this->db->where("{$relation}.id_company = '{$company_id}' ");
    $this->db->where("{$services}.category = '{$services_category}' ");
    $this->db->orderby("{$services}.name");
    $query = $this->db->get();

        if ($query->num_rows() > 0) {
        $result = $query->result_array();
            return $result;
        } else {
            return false;
        }
    }else{
      return false;
    }
  }
  function get_company_all_services($company_id){
    if (!empty($company_id)) {
    $relation = $this->directory_services_relationship_table;
    $this->db->from($relation);
    $this->db->where("{$relation}.id_company = '{$company_id}'");
   
    $query = $this->db->get();

        if ($query->num_rows() > 0) {
        $result = $query->result_array();
            return $result;
        } else {
            return false;
        }
    }else{
      return false;
    }
  }
  
  function get_company_services_orderbytype($company_id)
  {
	   if (!empty($company_id)) 
	   {
		   $sql = "SELECT service_type,services,name FROM `directory_services` WHERE `id` in (SELECT `id_services` FROM `directory_services_relationship` WHERE `id_company` = '".$company_id."') order by `service_type` asc";
			
			$query = $this->db->query($sql);
			//echo ($this->db->last_query());
			if ($query->num_rows() > 0) {
				$result = $query->result();            
				return $result;
				//return $this->db->last_query();
			} else {
				return false;
			}
			
		}else{
		  return false;
		}
  }
  function get_services($services_category = 'products'){
    if (!empty($services_category)) {
    $services = $this->directory_services_table;
    $relation = $this->directory_services_relationship_table;    
    $this->db->from($services);
    $this->db->where("{$services}.category = '{$services_category}' ");
    $this->db->order_by('name','asc');
    $query = $this->db->get();

        if ($query->num_rows() > 0) {
        $result = $query->result_array();
            return $result;
        } else {
            return false;
        }
    }else{
      return false;
    }
  }
  
  function get_services_print()
  {
    $services_print = $this->directory_services_table;
        
    $this->db->from($services_print);
    $this->db->where(array('service_type' => 'print' ));
    $this->db->order_by('name','asc');
    
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
    $result = $query->result_array();
        return $result;
    } else {
        return false;
    }
  }
  
  function get_services_web()
  {
    $services_print = $this->directory_services_table;
        
    $this->db->from($services_print);
    $this->db->where(array('service_type' => 'web' ));
    $this->db->order_by('name','asc');
    
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
    $result = $query->result_array();
        return $result;
    } else {
        return false;
    }
  }
  
  function update_services($company_id, $services){
    $relation = $this->directory_services_relationship_table;
    $main_table = $this->directory_services_table;
    if (!empty($company_id)){
           
      $categorytypeArr = $this->uri->uri_to_assoc(4);
      $categorytype = $categorytypeArr['service'];
      if($categorytype !='brands')
        $categorytype .= 's';
      
      $catQ = $this->db->get_where($main_table,array("category" => $categorytype));
                
                
                if($catQ->num_rows() > 0){
                    $catRes = $catQ->result_array();
                    $blank_arr = array();
                    
                    foreach($catRes as $result){
                        array_push($blank_arr, $result['id']);
                    }
                    
                    $arrStr = implode(',',$blank_arr);
                    $appendStr = "AND `id_services` IN (".$arrStr.")";
                }
                else{
                    $appendStr = '';
                }                
                
        
     
      
        
      $sqlDelete = "DELETE FROM {$relation} WHERE `id_company` = {$company_id} {$appendStr}";
      
      $this->db->query($sqlDelete);      
      
      //$this->db->delete($relation, array('id_company'=>$company_id));
   if(!empty($services))   
    foreach($services as $key=>$value){
    $array_insert = array(
        'id_company'=>$company_id,
        'id_services'=>$value
    );
    $this->db->insert($relation, $array_insert);
      }
      return TRUE;
    }else{
      return false;
    }
  }
  function get_company_news($company_id){
    if (!empty($company_id)) {
      $news = $this->directory_news_table;
      $this->db->from($news);
      $this->db->where($news.'.id_company',$company_id);
      $query = $this->db->get();
        if ($query->num_rows() > 0) {
        $result = $query->result_array();
            return $result;
        } else {
            return false;
        }
    }else{
      return false;
    }
  }
  function get_company_photos($company_id){
    if (!empty($company_id)) {
      $photos = $this->directory_photo_table;
      $this->db->from($photos);
      $this->db->where($photos.'.id_company',$company_id);
      $query = $this->db->get();
        if ($query->num_rows() > 0) {
        $result = $query->result_array();
            return $result;
        } else {
            return false;
        }
    }else{
      return false;
    }
  }
  function send_contact_email($name,$email,$telephone,$message,$send_to,$referrerURL,$isbranch=false){
    $config = array(
      'mailtype'=>'html'
    );
    $data = array(
      'name'=>$name, 
      'email'=>$email, 
      'telephone'=>$telephone, 
      'message'=>$message,
      'link' => $referrerURL 
    );
    $email_content = $this->load->view(TEMPLATE_NAME.'/front/mail/directory_lead_email',$data, true);
    $email_sender_content = $this->load->view(TEMPLATE_NAME.'/front/mail/directory_lead_email_tosender',$data,true);
    $this->load->library('email');
    $this->email->initialize($config);
    
    $this->email->from('noreply@mykprisguide.com', NOREPLY_ADD);
    $this->email->to($send_to);
    $this->email->reply_to($email);    
    //$this->email->cc('another@another-example.com');
    //$this->email->bcc('them@their-example.com');
    $this->email->subject(NOREPLY_ADD.' - New lead from directory');
    $this->email->message($email_content);    
    $this->email->send(); // -- SEND FIRST TO COMPANY
    
    // -- to sender 
    if(!$isbranch){
        $this->email->to($email);
        $this->email->reply_to('');
        $this->email->subject(NOREPLY_ADD.' - Lead email successfully submitted');
        $this->email->message($email_sender_content);    
        if($this->email->send()){
          return TRUE;
        }else{
          return FALSE;
        }
    }
     return TRUE;
  }
  /*
   * method get_user
   * @description untuk mengambil data detail dari tabel directory user
   * @input  user_id integer
   * @output row array atau false jika tidak ditemukan
   * @note harus dipindah ke model directory user
   */
  function get_user($user_id){
    $this->db->where("id", $user_id);
    $query = $this->db->get($this->directory_user_table);
        if ($query->num_rows() > 0) {
        $result = $query->row_array();
            return $result;
        } else {
            return false;
        }
  }
  /**
  * function get_list_company_order
  * get list company for admin page
  * 
  * @param mixed $order_by
  * @param mixed $limit
  * @param mixed $offset
  * @param mixed $active
  * 
  * @return result array
  */
  function get_list_company_order($order_by = "", $limit = 20, $offset = 0, $active = 'active'){       
    $city = $this->city_table;
    $directory_user = $this->directory_user_table;
    $directory_company = $this->directory_company_table;
    //$this->db->select("{$directory_company}.*, {$city}.name as city_name");
    $this->db->select("{$directory_company}.*");
    $this->db->from($directory_company);
    $this->db->join($directory_user,"{$directory_company}.id = {$directory_user}.id",'left');    
    //$this->db->join($city,"{$city}.id = {$directory_company}.city_id",'left');
    /*
    if($active == 'active'){
        $this->db->where("{$directory_user}.paid_until >= NOW() ");    
    }else{
        $this->db->where("{$directory_user}.paid_until < NOW() ");    
    }
    */
    if($active == 'active'){
        $this->db->where("{$directory_user}.activation_status = '1' ");    
    }else{
        $this->db->where("{$directory_user}.activation_status = '0' ");    
    }    
    $order = split("_",$order_by);
    if($order[1]){
        switch($order_by){
        case"date_asc":
            //$order = " ORDER BY user.post_date ASC";
            $this->db->order_by("{$directory_company}.last_edited ",'ASC');
        break;
        case"date_desc":
            //$order = " ORDER BY user.post_date DESC";
            $this->db->order_by("{$directory_company}.last_edited ",'DESC');
        break;
        case"company_asc":
            //$order = " ORDER BY company.company ASC";
            $this->db->order_by("{$directory_company}.company ",'ASC');
        break;
        case"email_asc":
            //$order = " ORDER BY company.company ASC";
            $this->db->order_by("{$directory_user}.email ",'ASC');
        break;        
        }
    }else{
        //$order_alpha = " AND company.company LIKE '$order[0]%'";
        $this->db->like("{$directory_company}.company", $order[0], 'after');
        $this->db->order_by("{$directory_company}.company ",'ASC'); 
    }
    //$this->db->order_by("{$directory_company}.last_edited ",'DESC');    
    $this->db->limit($limit, $offset);
    $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            //$result = $this->db->last_query();
            return $result;
        } else {
            return false;
        }   
  }
  /**
  * method function get_count_company_order
  * total list company for admin page 
  * 
  * @param mixed $order_by
  * @param mixed $active
  * 
  * @return integer
  */
  function get_count_company_order($order_by = "", $active = 'active'){        
    $city = $this->city_table;
    $directory_user = $this->directory_user_table;
    $directory_company = $this->directory_company_table;
    $this->db->select("{$directory_company}.*, {$city}.name as city_name");    
    $this->db->from($directory_company);
    $this->db->join($directory_user,"{$directory_company}.id = {$directory_user}.id",'left');    
    $this->db->join($city,"{$city}.id = {$directory_company}.city_id",'left');
    /*
    if($active == 'active'){
        $this->db->where("{$directory_user}.paid_until >= NOW() ");    
    }else{
        $this->db->where("{$directory_user}.paid_until < NOW() ");    
    }
    */
    if($active == 'active'){
        $this->db->where("{$directory_user}.activation_status = '1' ");    
    }else{
        $this->db->where("{$directory_user}.activation_status = '0' ");    
    }
    $order = split("_",$order_by);
    if($order[1]){
        switch($order_by){
        case"date_asc":            
            $this->db->order_by("{$directory_company}.last_edited ",'ASC');
        break;
        case"date_desc":            
            $this->db->order_by("{$directory_company}.last_edited ",'DESC');
        break;
        case"company_asc":            
            $this->db->order_by("{$directory_company}.company ",'ASC');
        break;
        case"email_asc":
            $this->db->order_by("{$directory_user}.email",'ASC');
        break;        
        }
    }else{
        $this->db->like("{$directory_company}.company", $order[0], 'after');
        $this->db->order_by("{$directory_company}.company ",'ASC'); 
    }
    //echo $this->db->last_query();    
    return $this->db->count_all_results();
  }
  /**
   * method check_available_email
   * @description checek wether the email available / email exist. mainly used in registration
   * input email string
   * output Boolean TRUE if email doesn't exist/available for registration
   *
   */
  function check_available_email($email){
    $this->db->where("email",$email);
    $query = $this->db->get($this->directory_user_table);
        if ($query->num_rows() > 0) {
        return FALSE;
        } else {
            return TRUE;
        }
  }
  /**
   * method register_user
   * @description hence the name!, this function register new user. assuming that all input are filtered beforehand
   * input email string
   * output Boolean TRUE if email doesn't exist/available for registration
   *
   */
  function register_user($email,$password){
    $activation = $this->generate_activation_data($email,$password);

    $arrayData = array(
    "email"=>$email,
        "md5username"=>md5($email),
    "password"=>md5($password),
    "password_real"=>$password,
    "user_group"=>'3',
    "post_date"=>date('Y-m-d H:i:s'),
    "activation_id"=>$activation['id'],
    ); 
    $this->db->insert($this->directory_user_table, $arrayData);
    $user_id = $this->db->insert_id();
    $array_company = array(
    'id'=>$user_id,
    'latitude'=>'-26.20000000000000',
    'longitude'=>'28.08300000000000',
    "last_edited"=>date('Y-m-d H:i:s'),
    );
    $this->db->insert($this->directory_company_table,$array_company);
    $this->send_activation_email($email,$email,$activation['link']);
    //return $activation;
	return $user_id;
  }
  function generate_activation_data($email, $password){
      $activation_id = md5($email.$password.date('U'));
      $activation_link = site_url('directory/activate/'.md5($email).'/'.$activation_id);
      $result = array(
          'id'=>$activation_id,
          'link'=>$activation_link
          );
      return $result;
  }
  function send_activation_email($email,$name,$activation_link){
    $config = array(
      'mailtype'=>'html'
    );
    $data = array(
      'name'=>$name, 
      'email'=>$name, 
      'activation_link'=>$activation_link, 
    );
    $email_content = $this->load->view(TEMPLATE_NAME.'/front/mail/directory_activation_email',$data, true);
    $this->load->library('email');
    $this->email->initialize($config);
    
    $this->email->from('noreply@mykprisguide.com', NOREPLY_ADD);
    $this->email->to($email);
    //$this->email->cc('another@another-example.com');
    //$this->email->bcc('them@their-example.com');
    $this->email->subject(NOREPLY_ADD.' - Directory activation email ');
    $this->email->message($email_content);
    if($this->email->send()){
      return TRUE;
    }else{
      return FALSE;
    }
  }
  function activate_user($email_md5, $activation_id){
    $this->db->where('activation_id',$activation_id);
    $this->db->where('activation_status','0');
    $query = $this->db->get($this->directory_user_table);
    
    
    if($query->num_rows() > 0 ){
      $user_detail = $query->row_array();
      if(md5($user_detail['email']) == $email_md5 ){
    $array_activate = array(
        'activation_status'=>'1',
        'last_edited'=>date("Y-m-d H:i:s"),
        );
                
    $this->db->update($this->directory_user_table, $array_activate);
    
   
    return true;
      }else{
    return false;
      }
      return true;
    }else{
      return false;
    }
  }
    /**
    * method delete_company
    * delete directory company and all table with prefix directory that contain
    * id/company_id like $company_id
    * 
    * @param mixed $company_id
    * @return string $debug
    */
    function delete_company($company_id){ 
        // delete directory news, and unlink the image
        $this->db->where('id_company',$company_id);        
        $query = $this->db->get($this->directory_news_table);
        $debug = $this->db->last_query();
        
        if($query->num_rows() > 0){
            $directory_news = $query->result_array();
            foreach($directory_news as $news){
                for($i=1;$i<=4;$i++){
                    $field = 'image_'.$i;
                    if(!empty($news[$field])){
                        if (file_exists($this->news_folder_url.$news[$field])){
                        unlink($this->news_folder_url.$news[$field]);    
                        unlink($this->news_thumb_folder_url.$news[$field]);                           
                        }
                    }                       
                }    
            }        
        }  
        $this->db->delete($this->directory_news_table, array('id_company' => $company_id));
        $debug .= "<br />".$this->db->last_query();     
        // delete directory photo, and unlink the image
        $this->db->where('id_company',$company_id);        
        $query = $this->db->get($this->directory_photo_table);
        $debug .= "<br />".$this->db->last_query();
        if($query->num_rows() > 0){
            $directory_photo = $query->result_array();
            foreach($directory_photo as $photo){                
                if(!empty($photo['image'])){
                    if (file_exists($this->photos_folder_url.$photo['image'])){
                    unlink($this->photos_folder_url.$photo['image']);    
                    unlink($this->photos_thumb_folder_url.$photo['image']);                           
                    }
                }               
            }        
        }  
        $this->db->delete($this->directory_photo_table, array('id_company' => $company_id));
        $debug .= "<br />".$this->db->last_query();
        // delete directory branch  
        //$this->db->delete($this->directory_branch_table, array('id_company' => $company_id));
        //$debug .= "<br />".$this->db->last_query();
        // delete directory services relationship        
        $this->db->delete($this->directory_services_relationship_table, array('id_company' => $company_id));
        $debug .= "<br />".$this->db->last_query();
        // delete directory invoice        
        $this->db->delete($this->directory_invoice_table, array('id_user' => $company_id));
        $debug .= "<br />".$this->db->last_query();
        // delete directory company , and unlink the image        
        $this->db->where('id',$company_id);        
        $query = $this->db->get($this->directory_company_table);
        $debug .= "<br />".$this->db->last_query();
        if($query->num_rows() > 0){
            $directory_company = $query->result_array();
            foreach($directory_company as $company){                
                if(!empty($company['company_logo'])){
                    if (file_exists($this->logo_folder_url.$company['company_logo'])){
                    unlink($this->logo_folder_url.$company['company_logo']);    
                    unlink($this->logo_thumb_folder_url.$company['company_logo']);                           
                    }
                }               
            }        
        }  
        $this->db->delete($this->directory_company_table, array('id' => $company_id));
        $debug .= "<br />".$this->db->last_query();
        // delete directory user        
        $this->db->delete($this->directory_user_table, array('id' => $company_id));
        $debug .= "<br />".$this->db->last_query();
        return $debug;
    }
    
    function get_company_detail($company_id){
        if (!empty($company_id)) {        
            $query = $this->db->get_where($this->directory_company_table, array('id' => $company_id));
           
            if ($query->num_rows() > 0) {
                $result = $query->row_array();
                return $result;
            } else {
                return false;
            }
        }
    }    
    
    /*
     * method set_company_activation();
     * @description set company listing status on front end (active by default, or inactive the otherwise)
     *
     *
     */
    function set_company_activation($company_id, $activation = 'activate'){
        $listing_active = ($activation == 'activate') ? '1' : '0';       
        $array_update = array('listing_active' => $listing_active);
        $this->db->where('id',$company_id);
        $this->db->update($this->directory_company_table, $array_update);        
        if ($activation == 'activate') {
            $query = $this->db->get_where($this->directory_user_table, array('id' => $company_id));
            $user_company = $query->row_array();
            if(!empty($user_company['paid_until'])){
                if($user_company['paid_until'] < date('Y-m-d H:i:s')){
                    $tomorrow  = date('Y-m-d H:i:s', mktime(0, 0, 0, date("m")  , date("d")+1, date("Y")));            
                    $array_update = array('paid_until' => $tomorrow);
                    $this->db->where('id',$company_id);
                    $this->db->update($this->directory_user_table, $array_update);    
                }    
            }else{
                $tomorrow  = date('Y-m-d H:i:s', mktime(0, 0, 0, date("m")  , date("d")+1, date("Y")));
                $array_update = array('paid_until' => $tomorrow);
                $this->db->where('id',$company_id);
                $this->db->update($this->directory_user_table, $array_update);
            }
        }else{            
            $yesterday  = date('Y-m-d H:i:s', mktime(0, 0, 0, date("m")  , date("d")-1, date("Y")));            
            $array_update = array('paid_until' => $yesterday);
            $this->db->where('id',$company_id);
            $this->db->update($this->directory_user_table, $array_update);
        }    
    }
     /*
      * method get_view_count
      * @description get company's view and lead count, where view is added when their page is viewed
      *     and lead count is how many email sent throught message system in their advertorial page
      * return boolean true/ they listed , false/they're unlisted
      *
      */
    function get_view_count($company_id){
    $this->db->select('view_count, lead_count');
    $this->db->where('id',$company_id);
    $query = $this->db->get($this->directory_company_table);
    if($query->num_rows() > 0){
        $result  = $query->row_array();
        return $result;
    }else{
        return FALSE;
    }
    }
    
    function set_admin_note($company_id, $text) {        
        $array_update = array('admin_note' => $text);        
        $this->db->where('id',$company_id);
        $this->db->update($this->directory_company_table, $array_update);
    }
     /*
      * method get_listing_status
      * @description get company's listing status (whether they're showing up in search, or could be viewed)
      * return boolean true/ they listed , false/they're unlisted
      *
      */
    function get_listing_status($company_id){
    $this->db->select('listing_active');
    $this->db->where('id',$company_id);
    $query = $this->db->get($this->directory_company_table);        
    if($query->num_rows() > 0){
        $result  = $query->row_array();
        
        if($result['listing_active'] == 1){
            return TRUE;
        }else{
            return FALSE;
        }
    }else{
        return FALSE;
    }
    }
     /*
      * method get_listing_status
      * @description get company's listing status (whether they're showing up in search, or could be viewed)
      * return boolean true/ they listed , false/they're unlisted
      *
      */
    function get_subscription_type($subscription_type = ''){
    if($subscription_type != ''){
    $this->db->where('id',$subscription_type);
    }else{
    }
    $this->db->order_by('id');//added.
    $query = $this->db->get($this->directory_subscription_type_table);
    if($query->num_rows() > 0){
        if($subscription_type != ''){
            return $query->row_array();
        }else{
            return $query->result_array();
        }
    }else{
        return FALSE;
    }
    }
    /**
    * function check_available_company
    * check available company name
    * 
    * @param mixed $company
    * @return boolean
    */
    function check_available_company($company = '',$current=''){        
        if($company == $current)
            return TRUE;
                      
        $this->db->where('company_slug_name', $company);
        $this->db->from($this->directory_company_table);         
        $search_result = $this->db->count_all_results();
        
        if($search_result > 0){
            return FALSE;
        }else{
            return TRUE;
        }
    }
    /**
    * function add_view_count
    * if user view the detail company page add the view_count by 1
    * 
    * @param mixed $company_id
    */
    function add_view_count($company_id){
        $this->db->select('view_count,view_session_id');
        $this->db->where('id',$company_id);
        $query = $this->db->get($this->directory_company_table);
        $result  = $query->row_array();
        $session_id = $this->session->userdata('session_id');
        if($result['view_session_id']!= $session_id){
          $update_view = $result['view_count'] + 1;            
          $array_update = array('view_count' => $update_view, 'view_session_id' => $session_id);
          $this->db->where('id',$company_id);
          $this->db->update($this->directory_company_table, $array_update);
        }
    }
    /**
    * function add_lead_count
    * if user view the detail company page add the lead_count by 1
    * 
    * @param mixed $company_id
    */
    function add_lead_count($company_id){
        $this->db->select('lead_count'); 
        $this->db->where('id',$company_id);
        $query = $this->db->get($this->directory_company_table);
        $result  = $query->row_array();
        
        $update_view = $result['lead_count'] + 1;    
        $array_update = array('lead_count' => $update_view);        
        $this->db->where('id',$company_id);
        $this->db->update($this->directory_company_table, $array_update);
    }
    
    private function get_region_name($regionId){
        $this->db->select('name');        
        $query = $this->db->get_where('region',array("id"=>$regionId));        
        $result = $query->row_object();
        return $result->name;
        
        
    } 
	
	function get_newest_company_json($limit= 10, $offset = 0, $province = '', $services = '', $search = ''){      
		   
			if(!empty($services) && $services != "'all'"){
				
				$sql_services = "WHERE id_services IN ($services) ";            
			}
			/*$sql = "SELECT d_user.paid_until , d_company.*, region.name as region_name , province_name,
					city.name as city_name_,city_name ";*/
			$sql = "SELECT d_user.paid_until , d_company.id, d_company.company,d_company.company_slug_name,d_company.company_website,d_company.latitude,d_company.longitude ";
			$sql .= "FROM directory_company AS d_company ";
			$sql .= "LEFT JOIN directory_user AS d_user ON d_user.id = d_company.id ";
			$sql .= "LEFT JOIN region AS region ON region.id = d_company.region_id ";
			//$sql .= "LEFT JOIN city AS city ON city.id = d_company.city_id ";
			//$sql .= "LEFT JOIN ( SELECT id_company ";
			$sql .= "INNER JOIN ( SELECT id_company ";
			$sql .= "FROM directory_services_relationship ";
			//$sql .= "WHERE id_services IN ('1') ";
			if(!empty($sql_services)){
				$sql .= $sql_services;
			}
			
			$sql .= "GROUP BY id_company) AS rel ON rel.id_company = d_company.id ";        
			$sql .= "WHERE d_user.paid_until > NOW() ";
			if((!empty($province)) && $province != 'all_province'){
				$sql_province = "AND region.id = $province ";
				$sql .= $sql_province;
			}
			if(!empty($search)){
				$sql_search = "AND d_company.company LIKE '%$search%' ";
				$sql .= $sql_search;
			}
			//$sql .= "AND region.id = 3684;";
			$sql .= "LIMIT $offset, $limit";
			$query = $this->db->query($sql);
			//echo ($this->db->last_query());
			if ($query->num_rows() > 0) {
				$result = $query->result_array();            
				return $result;
				//return $this->db->last_query();
			} else {
				return false;
			}
	  }  
	
}

/* End of file user_company_model.php */
/* Location: ./system/application/models/user_network_model.php */
?>